﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Training.Library.Interfaces;
using Training.Library.DomainObject.Administration.Catalog;
using System.Data.SqlClient;
using System.Data;
using Training.Library.Enum;
using Training.Library.DomainObject.Transaction;

namespace Training.Library.DataAccess.Administration.Catalog
{
    public partial class PelatihanRepo : ISearchable<Pelatihan>
    {
        public List<Pelatihan> GetList(int pageSize, int currentPage, string sortName, string sortOrder,string parameters)
        {
            List<Pelatihan> tt = new List<Pelatihan>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      PelatihanID, NamaPelatihan, TglPelatihan,TglAkhirPelatihan, TempatPelatihan, Pelatihan.PembicaraID,Pelatihan.CreatedBy, Pelatihan.CreatedDate, Pelatihan.UpdatedBy, Pelatihan.UpdatedDate,Pembicara.NamaPembicara,Cabang.NamaCabang ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Pelatihan LEFT JOIN master.Pembicara ON Pelatihan.PembicaraID=Pembicara.PembicaraID LEFT JOIN master.Cabang ON Pelatihan.CabangID = Cabang.CabangID ");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Pelatihan t = new Pelatihan(true);
                                t.PelatihanID = dr[Pelatihan.Fields.PelatihanID] == DBNull.Value ? 0 : int.Parse(dr[Pelatihan.Fields.PelatihanID].ToString());
                                t.NamaPelatihan = dr[Pelatihan.Fields.NamaPelatihan] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.NamaPelatihan].ToString();
                                t.TglPelatihan = dr[Pelatihan.Fields.TglPelatihan] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.TglPelatihan].ToString());
                                t.TglAkhirPelatihan = dr[Pelatihan.Fields.TglAkhirPelatihan] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.TglAkhirPelatihan].ToString());
                                t.TempatPelatihan = dr[Pelatihan.Fields.TempatPelatihan] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.TempatPelatihan].ToString();
                                t.PembicaraID = dr[Pelatihan.Fields.PembicaraID] == DBNull.Value ? (int?)null : int.Parse(dr[Pelatihan.Fields.PembicaraID].ToString());
                                t.CreatedBy = dr[Pelatihan.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[Pelatihan.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[Pelatihan.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[Pelatihan.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.UpdatedDate].ToString());

                                t.Pembicara.NamaPembicara = dr[Pembicara.Fields.NamaPembicara] == DBNull.Value ? string.Empty : dr[Pembicara.Fields.NamaPembicara].ToString();
                                t.Cabang.NamaCabang = dr[Cabang.Fields.NamaCabang] == DBNull.Value ? string.Empty : dr[Cabang.Fields.NamaCabang].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                       
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.Pelatihan {1}", column,parameters);
                            break;
                       
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public bool Save(Pelatihan domain, List<PelatihanDetail> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Insert Data Competency


                    string query = "SP_Pelatihan_Insert"; //"INSERT INTO master.Pelatihan(NamaPelatihan, TglPelatihan,TglAkhirPelatihan TempatPelatihan, PembicaraID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@NamaPelatihan, @TglPelatihan,@TglAkhirPelatihan @TempatPelatihan, @PembicaraID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@NamaPelatihan", SqlDbType.VarChar).Value = (object)domain.NamaPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TglPelatihan", SqlDbType.DateTime).Value = (object)domain.TglPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TglAkhirPelatihan", SqlDbType.DateTime).Value = (object)domain.TglAkhirPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TempatPelatihan", SqlDbType.VarChar).Value = (object)domain.TempatPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@PembicaraID", SqlDbType.Int).Value = (object)domain.PembicaraID ?? DBNull.Value;
                    cmd.Parameters.Add("@CabangID", SqlDbType.Int).Value = (object)domain.CabangID ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Insert Data Pelatihan Detail


                    #region Ambil CompetencyID

                    string queryPelatihan = "SP_Pelatihan_Select_By_CreatedBy_CreatedDate";// @"SELECT PelatihanID FROM master.Pelatihan WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate";
                    SqlCommand cmdPelatihan = new SqlCommand(queryPelatihan, conn);
                    cmdPelatihan.CommandType = CommandType.StoredProcedure;
                    cmdPelatihan.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdPelatihan.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                    cmdPelatihan.Transaction = txn;
                    var pelatihanID = cmdPelatihan.ExecuteScalar();
                    int resultpelatihanID = pelatihanID == DBNull.Value ? 0 : int.Parse(pelatihanID.ToString());
                    cmdPelatihan.Dispose();

                    #endregion


                    foreach (PelatihanDetail data in list)
                    {


                        query = "SP_PelatihanDetail_Insert";// "INSERT INTO transactions.PelatihanDetail(PelatihanID, PesertaID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@PelatihanID, @PesertaID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                        SqlCommand cmdDetail = new SqlCommand(query, conn);
                        cmdDetail.CommandType = CommandType.StoredProcedure;
                        //cmdDetail.CommandType = CommandType.Text;
                        cmdDetail.Parameters.Add("@PelatihanID", SqlDbType.Int).Value = resultpelatihanID;
                        cmdDetail.Parameters.Add("@PesertaID", SqlDbType.Int).Value = (object)data.PesertaID ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;

                        cmdDetail.Transaction = txn;
                        cmdDetail.ExecuteNonQuery();
                        cmdDetail.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool Update(Pelatihan domain, List<PelatihanDetail> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Insert Data Competency


                    string query = "SP_Pelatihan_Update";// "UPDATE master.Pelatihan SET NamaPelatihan = @NamaPelatihan, TglPelatihan = @TglPelatihan,TglAkhirPelatihan=@TglAkhirPelatihan TempatPelatihan = @TempatPelatihan, PembicaraID = @PembicaraID, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate WHERE PelatihanID = @PelatihanID";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@PelatihanID", SqlDbType.Int).Value = domain.PelatihanID;
                    cmd.Parameters.Add("@NamaPelatihan", SqlDbType.VarChar).Value = (object)domain.NamaPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TglPelatihan", SqlDbType.DateTime).Value = (object)domain.TglPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TglAkhirPelatihan", SqlDbType.DateTime).Value = (object)domain.TglAkhirPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@TempatPelatihan", SqlDbType.VarChar).Value = (object)domain.TempatPelatihan ?? DBNull.Value;
                    cmd.Parameters.Add("@PembicaraID", SqlDbType.Int).Value = (object)domain.PembicaraID ?? DBNull.Value;
                    cmd.Parameters.Add("@CabangID", SqlDbType.Int).Value = (object)domain.CabangID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Delete Pelatihan Detail All


                    query = "SP_PelatihanDetail_Delete_By_PelatihanID";// "DELETE transactions.PelatihanDetail WHERE PelatihanID=@PelatihanID";
                    SqlCommand cmdDel = new SqlCommand(query, conn);
                    //cmdDel.CommandType = CommandType.Text;
                    cmdDel.CommandType = CommandType.StoredProcedure;
                    cmdDel.Parameters.Add("@PelatihanID", SqlDbType.Int).Value = domain.PelatihanID;
                    

                    cmdDel.Transaction = txn;
                    cmdDel.ExecuteNonQuery();
                    cmdDel.Dispose();

                    #endregion

                    #region Insert Data Pelatihan Detail


                    foreach (PelatihanDetail data in list)
                    {


                        query = "SP_PelatihanDetail_Insert"; //"INSERT INTO transactions.PelatihanDetail(PelatihanID, PesertaID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@PelatihanID, @PesertaID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                        SqlCommand cmdDetail = new SqlCommand(query, conn);
                        cmdDetail.CommandType = CommandType.StoredProcedure;
                        //cmdDetail.CommandType = CommandType.Text;
                        cmdDetail.Parameters.Add("@PelatihanID", SqlDbType.Int).Value = domain.PelatihanID;
                        cmdDetail.Parameters.Add("@PesertaID", SqlDbType.Int).Value = (object)data.PesertaID ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmdDetail.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;

                        cmdDetail.Transaction = txn;
                        cmdDetail.ExecuteNonQuery();
                        cmdDetail.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public List<Pelatihan> GetListByPeserta(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<Pelatihan> tt = new List<Pelatihan>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      Pelatihan.PelatihanID, Pelatihan.NamaPelatihan, Pelatihan.TglPelatihan,Pelatihan.TglAkhirPelatihan, Pelatihan.TempatPelatihan, Pelatihan.PembicaraID,Pelatihan.CreatedBy, Pelatihan.CreatedDate, Pelatihan.UpdatedBy, Pelatihan.UpdatedDate,Pembicara.NamaPembicara,Cabang.NamaCabang ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Pelatihan LEFT JOIN master.Pembicara ON Pelatihan.PembicaraID=Pembicara.PembicaraID LEFT JOIN transactions.PelatihanDetail ON Pelatihan.PelatihanID = PelatihanDetail.PelatihanID LEFT JOIN master.Cabang ON Pelatihan.CabangID = Cabang.CabangID ");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Pelatihan t = new Pelatihan(true);
                                t.PelatihanID = dr[Pelatihan.Fields.PelatihanID] == DBNull.Value ? 0 : int.Parse(dr[Pelatihan.Fields.PelatihanID].ToString());
                                t.NamaPelatihan = dr[Pelatihan.Fields.NamaPelatihan] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.NamaPelatihan].ToString();
                                t.TglPelatihan = dr[Pelatihan.Fields.TglPelatihan] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.TglPelatihan].ToString());
                                t.TglAkhirPelatihan = dr[Pelatihan.Fields.TglAkhirPelatihan] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.TglAkhirPelatihan].ToString());
                                t.TempatPelatihan = dr[Pelatihan.Fields.TempatPelatihan] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.TempatPelatihan].ToString();
                                t.PembicaraID = dr[Pelatihan.Fields.PembicaraID] == DBNull.Value ? (int?)null : int.Parse(dr[Pelatihan.Fields.PembicaraID].ToString());
                                t.CreatedBy = dr[Pelatihan.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[Pelatihan.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[Pelatihan.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Pelatihan.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[Pelatihan.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Pelatihan.Fields.UpdatedDate].ToString());

                                t.Pembicara.NamaPembicara = dr[Pembicara.Fields.NamaPembicara] == DBNull.Value ? string.Empty : dr[Pembicara.Fields.NamaPembicara].ToString();
                                t.Cabang.NamaCabang = dr[Cabang.Fields.NamaCabang] == DBNull.Value ? string.Empty : dr[Cabang.Fields.NamaCabang].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalarByPeserta(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {

                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.Pelatihan INNER JOIN transactions.PelatihanDetail ON Pelatihan.PelatihanID = PelatihanDetail.PelatihanID  {1}", column, parameters);
                            break;

                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public DataTable GetDataTableReport()
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();

                    //sbQuery.AppendLine("SELECT   ROW_NUMBER() OVER(ORDER BY NamaPelatihan ASC) AS No,NamaPelatihan Nama,convert(varchar,TglPelatihan , 105) Tgl,TempatPelatihan Tempat,Pembicara.NamaPembicara Pembicara");
                    //sbQuery.AppendLine("FROM  master.Pelatihan INNER JOIN master.Pembicara ON Pelatihan.PembicaraID=Pembicara.PembicaraID ORDER BY NamaPelatihan ASC ");
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandText = sbQuery.ToString();
                    cmd.CommandText = "SP_Pelatihan_Report";
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }
    }
}
